<?php

namespace app\admin\controller\report;

use app\common\controller\Backend;
use app\admin\model\AdminDepart;
use think\Db;
use Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Wxlsx;

/**
 * 报表汇总管理
 *
 * @icon fa fa-user
 */
class Summary extends Backend
{

    protected $searchFields = 'id,reportname';

    /**
     * @var \app\admin\model\Report
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('ReportSummary');
		$this->detailModel = model('ReportDetails');
    }

	/**
     * 汇总
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
			
			$filter = json_decode($this->request->get("filter"),true);
			if(isset($filter['reporttime'])){
				$year = $filter['reporttime'];
				$filter['reporttime'] = strtotime($filter['reporttime'].'-1-1');
				$this->request->get(["filter"=>json_encode($filter)]);
			}else{
				$year = date('Y');
			}
			
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $list = $this->model
                ->where($where)
                ->order($sort, $order)
                ->paginate($limit);
			
            $result = array("total" => $list->total(), "rows" => $list->items(), "year" => $year);
			
            return json($result);
        }
		
		$this->assignconfig('year',date('Y'));
        return $this->view->fetch();
    }
	
	/**
     * 生成
     */
    public function add()
    {
		if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
		
		$this->token();
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        
        $map['reporttime'] = strtotime($params['reporttime'].'-1-1');
		$hasExist = $this->model->where($map)->find();
		if($hasExist)
		{
			$this->error(__('您已经生成过'.$params['reporttime'].'年度的报表汇总了，不能重复生成'));
		}

        $result = false;
        Db::startTrans();
        try {
            $result = $this->model->allowField(true)->save($params);
			
			$departs = AdminDepart::where('pid', '<>', 0)->column('id');
			$insertDetails = [];
			foreach($departs as $vo)
			{
				$data = array(
					'summary_id' => $this->model->id,
					'depart_id' => $vo,
				);
				$insertDetails[] = $data;
			}
			$this->detailModel->saveAll($insertDetails);
			
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success('生成成功', 'index');
    }
	
	/**
     * 删除
     */
    public function del($ids = "")
    {
        if (!$this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ? $ids : $this->request->post("ids");
        if (!$ids) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $count = $this->model->where('id', 'in', $ids)->delete();
		if ($count) {
			$this->detailModel->where('summary_id', 'in', $ids)->delete();
			$this->success();
		}

        $this->error(__('No rows were deleted'));
    }
	
	/**
     * 查看
     */
	public function view($ids)
	{
		//设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
			
            $list = $this->detailModel
				->with(['depart','summary'])
				->where('summary_id', $ids)
                ->where($where)
                ->order($sort, $order)
                ->paginate($limit);

			foreach($list as &$vo)
			{
				$budgetSummary = model('BudgetSummary')->where('budgettime', $vo['summary']['reporttime'])->find();
				if($budgetSummary)
				{
					//$map = [
					//		['summary_id', '=', $budgetSummary['id']], 
					//		['depart_id', '=', $vo['depart_id']],
					//		['status', '=', 1]
					//	];
					$map = array();
					$map['summary_id'] = $budgetSummary['id'];
					$map['depart_id'] = $vo['depart_id'];
					$map['status'] = 1;
					$budgetDetails = model('BudgetDetails')->where($map)->select();
					foreach($budgetDetails as $vo2)
					{
						if($vo2['budgettype'] == 1)
						{
							$vo['basicbudgetmoney'] = $vo2['budgetmoney'];
						}
						
						if($vo2['budgettype'] == 2)
						{
							$vo['projectbudgetmoney'] = $vo2['budgetmoney'];
						}
					}
				}
			}
			
            $result = array("total" => $list->total(), "rows" => $list->items());
			
            return json($result);
        }
		
		$row = $this->model->get($ids);
		$this->view->assign('row', $row);
        return $this->view->fetch();
	}
	
	/**
     * 导出
     */
	public function export($ids)
	{
		$row = $this->model->get($ids);
		$list = $this->detailModel
			->with(['depart','summary'])
			->where('summary_id', $ids)
			->order('id', 'desc')
			->select();

		foreach($list as &$vo)
		{
			$budgetSummary = model('BudgetSummary')->where('budgettime', $vo['summary']['reporttime'])->find();
			if($budgetSummary)
			{
				$map = array();
				$map['summary_id'] = $budgetSummary['id'];
				$map['depart_id'] = $vo['depart_id'];
				$map['status'] = 1;
				$budgetDetails = model('BudgetDetails')->where($map)->select();
				foreach($budgetDetails as $vo2)
				{
					if($vo2['budgettype'] == 1)
					{
						$vo['basicbudgetmoney'] = $vo2['budgetmoney'];
					}
					
					if($vo2['budgettype'] == 2)
					{
						$vo['projectbudgetmoney'] = $vo2['budgetmoney'];
					}
				}
			}
		}
		
		$template = 'report_summary_template.xlsx';
		$filePath = ROOT_PATH . DS . 'public' . DS . $template;
        if (!is_file($filePath)) {
            $this->error(__('No file were found'));
        }
		
		$reader = new Xlsx();
		try{
            if (!$spreadsheet = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
			
			$sheet = $spreadsheet->getActiveSheet()->setTitle(date('Y',$row['reporttime']).'年'.$row['reportname']);
			$sheet->setCellValueByColumnAndRow(1, 1, date('Y',$row['reporttime']).'年'.$row['reportname']);
			$rows = 4;
			foreach($list as $key=>$vo)
			{
				if(!isset($vo->basicbudgetmoney)) $vo->basicbudgetmoney = 0;
				if(!isset($vo->projectbudgetmoney)) $vo->projectbudgetmoney = 0;
				
				$sheet->getRowDimension($rows)->setRowHeight(30);
				$sheet->setCellValueByColumnAndRow(1, $rows, $key+1);
				$sheet->setCellValueByColumnAndRow(2, $rows, $vo->depart->name);
				$sheet->setCellValueByColumnAndRow(3, $rows, $vo->basicbudgetmoney);
				$sheet->setCellValueByColumnAndRow(4, $rows, $vo->adjustbudgetamount);
				$sheet->setCellValueByColumnAndRow(5, $rows, $vo->executeamount);
				$sheet->setCellValueByColumnAndRow(6, $rows, ($vo->adjustbudgetamount!=0?round($vo->executeamount*100/$vo->adjustbudgetamount,2):'0.00').'%');
				$sheet->setCellValueByColumnAndRow(7, $rows, $vo->projectbudgetmoney);
				$sheet->setCellValueByColumnAndRow(8, $rows, $vo->alladjustbudgetamount-$vo->basicbudgetmoney);
				$sheet->setCellValueByColumnAndRow(9, $rows, $vo->allexecuteamount-$vo->executeamount);
				$sheet->setCellValueByColumnAndRow(10, $rows, (($vo->alladjustbudgetamount-$vo->basicbudgetmoney)!=0?round(($vo->allexecuteamount-$vo->executeamount)*100/($vo->alladjustbudgetamount-$vo->basicbudgetmoney),2):'0.00').'%');
				$sheet->setCellValueByColumnAndRow(11, $rows, $vo->basicbudgetmoney);
				$rows++;
			}
			
			$filename = date('Y',$row['reporttime']).'年'.$row['reportname'];
			// MIME 协议，文件的类型，不设置，会默认html
			header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			// MIME 协议的扩展
			header('Content-Disposition:attachment;filename='.$filename.'.xlsx');
			// 缓存控制
			header('Cache-Control:max-age=0');
			
			$writer = new Wxlsx($spreadsheet);
			$writer->save('php://output');
		} catch (Exception $exception) {
            $this->error($exception->getMessage());
        }	
	}
}
